I started this question on another thread and was directed here.  Hopefully you can offer some expert troubleshooting advice.

I have a 37 MB spreadsheet that tracks a few years worth of financial data (about 42000 rows and 40 columns).   In Excel 2010 32 Bit (on Windows 7 64 bit machine) it consumes approx. 1.2 GB of memory when loaded but runs ok.  I just "upgraded" to Excel 2013 32 bit (on Windows 8 64 bit machine) and when I try to load the sheet it loads and consumes a similar amount of memory.  However, when I try to refresh a pivot table in Excel 2013 and recalculate a few times the sheet I quickly get a "Not enough system resources to display completely".  Hard to believe given the Excel 2013 machine is a better machine.

What's up with Excel 2013?  Why does it not seem to be able to load/run the same sheets Excel 2010 32 bit could????  I like the new features but if I can't work with my main spreadsheet it's kind of a waste.

Any troubleshooting tips welcome.  I am trying to understand where the memory consumption is coming from.  Using FastExcel I can see the one sheet uses most of the memory but I can't understand why so much.  42000 rows is not a lot of data.  Your help would be greatly appreciated.

Thank You

cnuk,

  On that one sheet do you have any dynamic ranges?  The use of OFFSET and others like SUMPRODUCT or any formulas that are entered as Arrays' will consume a lot of resources.  I had one spreadsheet that I was using that used dynamic ranges and it took a long time to open and calculate.  So I used VBA to create a named range that was based on the actual length of the columns.  This cut down my calculation substantially.

Harry

No dynamic ranges but similar in the respect that it is one huge table for 42000 rows and 60 columns (not 40 like I said above).  In the past some of my experiments have me thinking tables are not very memory efficient in Excel but I can't prove that.  No OFFSET or SUMPRODUCT as array formulas.

OK, I just completed another experiment and the results are very interesting.

I have a stripped down version of the problem spreadsheet with 33000 rows and 60 columns.  It now loads and takes 850MB with the main sheet setup as an Excel Table.

I take the same spreadsheet, convert the table to a range with the same 33000 rows and 60 columns and the memory usage for Excel is now 345MB!!!  Yes 345MB.

Why on earth can using an Excel table cost you 500MB of additional memory usage?  This is not the first time I've come across this result so am beginning to believe it.  Has anyone read anything official that warns you about memory usage with tables?

cnuk,

  The only thing I can possibly think of is that excel stores and manages a table as an array.  Since a reference to the table is structured =Table Name[@Some Cell], it leads me to believe that the table is managed as an array and if I understand how array's work, excel will load the whole array into memory.

 Can you work without table and just use Named Ranges?

hth

Harry

cnuk,

  The only thing I can possibly think of is that excel stores and manages a table as an array.  Since a reference to the table is structured =Table Name[@Some Cell], it leads me to believe that the table is managed as an array and if I understand how array's work, excel will load the whole array into memory.

 Can you work without table and just use Named Ranges?

hth

Harry

• Proposed as answer by James Cone 1 hour 14 minutes ago

cnuk,

  The only thing I can possibly think of is that excel stores and manages a table as an array.  Since a reference to the table is structured =Table Name[@Some Cell], it leads me to believe that the table is managed as an array and if I understand how array's work, excel will load the whole array into memory.

 Can you work without table and just use Named Ranges?

hth

Harry

• Proposed as answer by James Cone Saturday, November 30, 2013 10:40 AM

I can go back to a named range.  I can setup dynamic named ranges and get back most of the functionality.  Unfortunately the file is big enough now that the "Convert To Range" option crashes excel due to the memory requirements but I can workaround that.  Just a shame because I like the features Excel Tables have to offer.  Seems like something that should be mentioned in the documentation.

cnuk,

  Someone gave me this code that goes through a number columns and sets the named range to the name in row 1.  That way you are not using dynamic ranges, but static ranges and you can run it anytime you update the data.

Public Sub MyNamedRanges()
    Dim LastRow As Long
    Dim rng As Range
    Dim MyCol As Long
    For MyCol = 1 To 28 'Change to suit the number columns
        LastRow = Cells(Rows.Count, MyCol).End(xlUp).Row
        Set rng = Range(Cells(3, MyCol), Cells(LastRow, MyCol))
        ActiveWorkbook.Names.Add Name:=Cells(1, MyCol).Value, RefersTo:=rng
    Next MyCol
End Sub

cnuk,

  Someone gave me this code that goes through a number columns and sets the named range to the name in row 1.  That way you are not using dynamic ranges, but static ranges and you can run it anytime you update the data.

Public Sub MyNamedRanges()
    Dim LastRow As Long
    Dim rng As Range
    Dim MyCol As Long
    For MyCol = 1 To 28 'Change to suit the number columns
        LastRow = Cells(Rows.Count, MyCol).End(xlUp).Row
        Set rng = Range(Cells(3, MyCol), Cells(LastRow, MyCol))
        ActiveWorkbook.Names.Add Name:=Cells(1, MyCol).Value, RefersTo:=rng
    Next MyCol
End Sub

• Marked as answer by Rex ZhangModerator Monday, March 04, 2013 7:18 AM

I am having the same problems, and I see no reference to it in any of the answers.

I use data connection to SQL as pivot table,

Things that works great with Excel 2010 just get stacked with Excel 2013 with out of memory

Things like group by... I have 17 Gb memory free and I use the 64 bit version, but still excel complains about memory

I have exactly the same problem as Cnuck with big pivot-tables in Excel 2013. "Not enough system resources to display completely"....? But it worked on Excel 2010 with the same machine. I don't use dynamic ranges or tables

Are there any solutions?

Mat



I had the same problem this week. I have about 350,000 rows of data and about ten fields that are formula (conditional, reference other variables). I have this set up as a table. (Not a pivot table)

I saved it every couple of  hour as a worked on it under a new name for version control - But I did not save close and reopen. After ten hours of work I closed it and when I tried to open it the same - not enough resources -  message came up. It is a 52MB file then accessed up to 1.2 GB to open and then it came up with message. I have to go back through each of 5 versions to find one that I could open. 

I used similar tables in 2010 but no problem.